<?php
/**
 * MySQL Lite
 * 
 * A light-weight MySQL framework for PHP
 * 
 * @package MySQL_Lite
 * @version 0.9
 * @author Daniel Fone <daniel@f1techware.co.nz>
 * @copyright 2008 Daniel Fone
 * @license GNU GPL http://www.gnu.org/licenses/gpl.html
 */

/**
 * MySQL is the main class used to connect to
 * and manage a MySQL server.
 * 
 * <b>Usage overview:</b>
 * <code><?php
 * // Connect to a database
 * $mysql = MySQL::connect("localhost", 'root', 'supreme');
 * 
 * // Get all databases
 * $dbcount = count($mysql->databases());
 * // Get existing database
 * $test_db = $mysql->database('test');
 * // Delete exisiting database
 * $mysql->dropDatabase('automotive');
 * $mysql->database('automotive')->drop(); // Same thing
 * // Create a new database
 * $autodb = $mysql->createDatabase('automotive');
 * ?></code>
 * 
 * @package MySQL_Lite
 * 
 */
class MySQL {
	private $host, $username, $password = '';
	private $link = null;
	private $databases = array();
	private $lastQuery, $error = '';
	
	/**
	 * @ignore
	 */
	function __construct($link, $host){
		$this->link = $link;
		$this->host = $host;
	}
	
	/**
	 * Get the hostname we're currently connected to
	 * 
	 * @return String Host
	 */
	function host(){
		return $this->host;
	}
	
	/**
	 * Get the latest error message
	 * 
	 * @return String Error
	 */
	function error(){
		return $this->error;
	}
	
	/**
	 * Connect to a MySQL server.
	 * 
	 * This will open a persistent connection to the
	 * specified MySQL server and return a MySQL object.
	 * eg.
	 * <code><?php
	 * 		$mysql = MySQL::connect('localhost', 'root', 'pass123');
	 * 		$mysql = MySQL::connect('mysql.example.com');
	 * ?></code>
	 * 
	 * @return MySQL A new MySQL object
	 * @param String $host MySQL hostname
	 * @param String $username MySQL username
	 * @param String $password MySQL password
	 */
	static function connect($host, $username='', $password=''){
		$link = mysql_pconnect($host, $username, $password);
		if ( $link ) return new MySQL($link, $host, $username, $password);
	}
	
	/**
	 * Execute a raw SQL query on the object.
	 * 
	 * eg.
	 * <code><?php
	 * 		$rows = $mysql->query('SHOW TABLES FROM `my_db`');
	 * 		foreach ( $rows as $row ) {
	 * 			print $row['Create_time'];
	 * 			print $row['0'];
	 * 		}
	 * ?></code>
	 * 
	 * @return Mixed If successful, returns an array of results, otherwise, returns false
	 * @param String SQL query to execute
	 */
	function query($query){
		$records = array();
		// Execute query
		$this->lastQuery = $query;
		$result = mysql_query($query, $this->link);
		// Error checking
		if ( ! $result ){
			$this->error = mysql_error();
			return false;
		} elseif ( $result === true ){
			return true;
		}
		
		// Iterate through results
		while ($record = mysql_fetch_array($result)) {
			$records[] = $record;
		}
		// Free resultset
		mysql_free_result($result);
		return $records;
	}
	
	/**
	 * Get the last query executed. Useful for debugging
	 * 
	 * @return String Query
	 */
	function lastQuery(){
		return $this->lastQuery;
	}
	
	/**
	 * Get the databases on this server.
	 * 
	 * Similar to 'SHOW DATABASES' but returns an
	 * array of MySQL_Database objects. eg.
	 * <code><?php
	 * 		foreach ( $db in $mysql->databases() ){
	 * 			print $database->name();
	 * 		}
	 * ?></code>
	 * 
	 * @return Array An array of MySQL_Database objects, keyed by name
	 */
	function databases(){
		if ( empty($this->databases) ){
			foreach ( $this->query('SHOW DATABASES') as $sqlDatabase ){
				$name = $sqlDatabase['Database'];
				$this->databases[$name] = new MySQL_Database($this, $name);
			}
		}
		return $this->databases;
	}
	
	/**
	 * Get a specific database on this server
	 * 
	 * eg.
	 * <code><?php $my_db = $mysql->database('my_db'); ?></code>
	 * 
	 * @return MySQL_Database The specified database
	 * @param String $name Name of the database
	 */
	function database($name){
		$databases = $this->databases();
		if ( isset($databases[$name]) ) return $databases[$name];
		return false;
	}
	
	/**
	 * Create a database on this server.
	 * 
	 * Similar to 'CREATE DATABASE'. eg.
	 * <code><?php $my_new_db = $mysql->createDatabase('my_new_db'); ?></code>
	 * 
	 * @return MySQL_Database The newly created database
	 * @param String $name Name of the database
	 */
	function createDatabase($name){
		if ( $this->query("CREATE DATABASE `$name`") === false ) return false;
		$database = new MySQL_Database($this, $name);
		if ( $this->databases ) $this->databases[$name] = $database;
		return $database;
	}
	
	/**
	 * Drop a specified database.
	 * 
	 * Similar to 'DROP DATABASE'. eg.
	 * <code><?php if ( ! $mysql->dropDatabase('my_old_db') ) die("Couldn't drop database"); ?></code>
	 * 
	 * @return Bool True on success, false on failure
	 * @param String $name Name of database
	 */
	function dropDatabase($name){
		if ( $this->query("DROP DATABASE `$name`") === false ) return false;
		unset($this->databases[$name]);
		return true;
	}
	
	/**
	 * Escape the specified string using a
	 * mysql_real_escape_string on this connection
	 * 
	 * @return String Escaped string
	 * @param String $string to escape
	 */
	function escape($string){
		return mysql_real_escape_string($string, $this->link);
	}
	
	/**
	 * Get the ID of the last inserted row
	 * 
	 * @return Integer ID
	 */
	function insertID(){
		return mysql_insert_id($this->link);
	}
}

/**
 * Class for managing MySQL databases
 * 
 * <b>Usage overview:</b>
 * <code><?php
 * // Create a new table
 * $autodb->createTable('trains');
 * $cars = $autodb->createTable('cars');
 * // Iterate through all tables
 * foreach ( $autodb->tables() as $table ){
 * 	$tablename = $table->name();
 * }
 * ?></code>
 * 
 * @package MySQL_Lite
 */
class MySQL_Database {
	private $name = '';
	private $mysql = null;
	private $tables = array();
	
	/**
	 * @ignore
	 */
	function __construct($connection, $name){
		$this->name = $name;
		$this->mysql = $connection;
	}
	
	/**
	 * Get the database name
	 * 
	 * @return String Database name
	 */
	function name(){
		return $this->name;
	}
	
	/**
	 * Gets the MySQL object this database belongs to
	 * @return MySQL Connection
	 */
	function mysql(){
		return $this->mysql;
	}
	
	/**
	 * Select this database for future queries. This should
	 * usually be called by other methods.
	 */
	function select(){
		return $this->mysql->query("USE `$this->name`");
	}
	
	/**
	 * Drop this database.
	 * eg.
	 * 		$mysql->database('my_db')->drop();
	 * Equivalent to
	 * 		$mysql->dropDatabase('my_db');
	 * 
	 * @return Bool True on success, false on failure
	 */
	function drop(){
		return $this->mysql->dropDatabase($this->name);
	}
	
	/**
	 * Get the tables in this database. Similar to
	 * 'SHOW TABLES' but returns an array of
	 * MySQL_Table objects.
	 * eg.
	 * 		foreach ( $table in $mysql->database('my_db')->tables() ){
	 * 			print $table->name();
	 * 		}
	 * 
	 * @return Array An array of MySQL_Table objects, keyed by name
	 */
	function tables(){
		if ( empty($this->tables) ){
			foreach ( $this->mysql->query("SHOW TABLES FROM `$this->name`") as $sqlTable ){
				$name = $sqlTable[0];
				$this->tables[$name] = new MySQL_Table($this, $name);
			}
		}
		return $this->tables;
	}
	
	/**
	 * Get a specific table in this database
	 * eg.
	 * 		$my_table = $mysql->database('my_db')->table('my_table');
	 * 
	 * @return MySQL_Table The specified table
	 * @param String $name Table name
	 */
	function table($name){
		$tables = $this->tables();
		if ( isset($tables[$name]) ) return $tables[$name];
		return false;
	}
	
	/**
	 * Create a table in this database. Similar to
	 * 'CREATE TABLE'. Includes a default primary key
	 * (`id` INT(11) AUTO_INCREMENT)
	 * eg.
	 * 		$my_new_table = $mysql->database('my_db')->createTable('my_new_table');
	 * 
	 * @return MySQL_Table The newly created table
	 * @param String $name Name of the table
	 */
	function createTable($name){
		$this->select();
		if ( $this->mysql->query("CREATE TABLE `$name` (`id` INT(11) AUTO_INCREMENT PRIMARY KEY)") === false ) return false;
		$table = new MySQL_Table($this, $name);
		if ( $this->tables ) $this->tables[$name] = $table;
		return $table;
	}
	
	/**
	 * Drop a specified table. Similar to 
	 * 'DROP TABLE'.
	 * eg.
	 * 		if ( ! $mysql->database('my_db')->dropTable('my_old_table') )
	 * 			die("Couldn't drop table");
	 * 
	 * @return Bool True on success, false on failure
	 * @param String $name Table name
	 */
	function dropTable($name){
		$this->select();
		if ( $this->mysql->query("DROP TABLE `$name`") === false ) return false;
		unset($this->tables[$this->name]);
		return true;
	}
}

/**
 * Class for manageing MySQL tables
 * 
 * <b>Usage overview:</b>
 * <code>
 * <?php
 *  * // Create fields on table
 * $cars->createField('maker',	'varchar(30)');
 * $cars->createField('model',	'varchar(30)');
 * $cars->createField('year',	'int(4)');
 * $cars->createField('color',	'varchar(30)');
 * // Modify table field
 * $cars->field('color')->name('colour');
 * 
 * // Define new record for table
 * $data = array(
 * 	'maker'		=> 'Shelby',
 * 	'model'		=> 'Mustang',
 * 	'year'		=> '1967',
 * 	'colour'	=> 'Silver'
 * );
 * // Add record to table
 * $eleanor = $cars->addRecord($data);
 * // Modify record
 * $eleanor->model = 'Mustang GT 500';
 * // Add another record
 * $data = array(
 * 	'maker'		=> 'Aston Martin',
 * 	'model'		=> 'DB5',
 * 	'year'		=> '1963',
 * 	'colour'	=> 'Silver'
 * );
 * $cars->addRecord($data);
 * 
 * // Convert our SQL table to and HTML table
 * ?>
 * <h1><?=$cars->name()?> on <?=$mysql->host()?></h1>
 * <table>
 * 	<tr>
 * 		<? foreach ( $cars->fields() as $field ): ?>
 * 			<th><?=$field->name()?></th>
 * 		<? endforeach; ?>
 * 	</tr>
 * 	<? foreach ( $cars->records() as $car ): ?>
 * 		<tr>
 * 			<? foreach ( $cars->fields() as $field ): ?>
 * 				<td><?=$car->{$field->name()}?></td>
 * 			<? endforeach; ?>
 * 		</tr>
 * 	<? endforeach; ?>
 * </table>
 * </code>
 * 
 * @package MySQL_Lite
 */
class MySQL_Table {
	private $name = '';
	private $database = null;
	private $mysql = null;
	private $fields = array();
	
	/**
	 * @ignore
	 */
	function __construct($database, $name){
		$this->database = $database;
		$this->name = $name;
		$this->mysql = $database->mysql();
	}
	
	/**
	 * Get/set the table name
	 * @return String Table name
	 * @param String $newName[optional] New name of table
	 */
	function name($newName=''){
		if ( $newName ){
			$this->database->select();
			if ( $this->mysql->query("RENAME `$this->name` TO `$newName`") == false ) return false;
			$this->name = $newName;
		}
		return $this->name;
	}
	
	/**
	 * Get this tables database
	 * 
	 * @return MySQL_Database Database
	 */
	function database(){
		return $this->database;
	}
	
	/**
	 * Get the MySQL object this table
	 * belongs to
	 * 
	 * @return MySQL Connection
	 */
	function mysql(){
		return $this->mysql;
	}
	
	/**
	 * Drop this table.
	 * eg.
	 * 		$mysql->database('my_db')->table('my_table')->drop();
	 * Equivalent to
	 * 		$mysql->database('my_db')->dropTable('my_table);
	 * 
	 * @return Bool True on success, false on failure
	 */
	function drop(){
		return $this->database->dropTable($this->name);
	}
	
	/**
	 * Get the fields for this table. Similar to
	 * 'DESCRIBE `table`' but returns an array of
	 * MySQL_Field objects.
	 * eg.
	 * 		foreach ( $field in $my_db->table('my_table')->fields() ){
	 * 			print $field->name();
	 * 		}
	 * 
	 * @return Array An array of MySQL_Field objects, keyed by name
	 */
	function fields(){
		if ( empty($fields) ){
			$this->database->select();
			foreach ( $this->mysql->query("DESCRIBE `$this->name`") as $sqlField ){
				$name = $sqlField['Field'];
				$type = $sqlField['Type'];
				$this->fields[$name] = new MySQL_Field($this, $name, $type);
			}
		}
		return $this->fields;
	}
	
	/**
	 * Get a specific field for this table
	 * eg.
	 * 		$field = $my_db->table('my_table')->field('field_name');
	 * 		print $field->type();
	 * 
	 * @return MySQL_Field The specified field
	 * @param String $name Field name
	 */
	function field($name){
		$fields = $this->fields();
		if ( isset($fields[$name]) ) return $fields[$name];
		return false;
	}
	
	/**
	 * Create a new field on this table. 
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$cars->createField('gross_weight', 'DECIMAL(12,6)');
	 * 
	 * @return MySQL_Field The new field
	 * @param String $name Field name
	 * @param String $type SQL Type
	 */
	function createField($name, $type){
		$this->database->select();
		if ( $this->mysql->query("ALTER TABLE `$this->name` ADD `$name` $type") == false ) return false;
		$field = new MySQL_Field($this, $name, $type);
		if ( $this->fields ) $this->fields[$name] = $field;
		return $field;
	}
	
	/**
	 * Drop a specified field.
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$cars->dropField('gross_weight');
	 * 
	 * @return Bool True on success, false on failure
	 * @param String $name Field name
	 */
	function dropField($name){
		$this->database->select();
		if ( $this->mysql->query("ALTER TABLE `$this->name` DROP `$name`") === false ) return false;
		unset($this->fields[$name]);
		return true;
	}
	
	/**
	 * Insert a new record into this table.
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$car = array(
	 * 			'maker'		=> 'Toyota',
	 * 			'model'		=> 'Corolla',
	 * 			'year'		=> '2008',
	 * 			'colour'	=> 'Silver'
	 *		);
	 * 		$cars->addRecord($car);
	 * 	 		
	 * @return Integer The id of the inserted record
	 * @param Array $sqlRecord An associative array using field names for keys
	 */
	function addRecord($sqlRecord){
		$myFields = $myVals = array();
		foreach ( $sqlRecord as $field => $val ){
			$myFields[] = "`$field`";
			$myVals[]	= "'".$this->mysql->escape("$val")."'";
			
		}
		$field_list = join(', ', $myFields);
		$value_list = join(', ', $myVals);
		$this->database->select();
		$ret = $this->mysql->query("INSERT INTO `$this->name` ($field_list) VALUES ($value_list)");
		if ( ! $ret ) return false;
		return $this->record($this->mysql->insertID());
	}
	
	/**
	 * Get all records from this table
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		foreach ( $cars->records() as $car ) {
	 * 			print $car->year;
	 * 		}
	 * 
	 * @return Array Records
	 */
	function records(){
		$this->database->select();
		$query = "SELECT * FROM `$this->name`";
		$records = $this->mysql->query($query);
		if ( ! $records ) return false;
		$myRecords = array();
		foreach ( $records as $record ){
			$myRecords[] = new MySQL_Record($this, $record);
		}
		return $myRecords;
	}
	
	/**
	 * Get a record by ID
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$my_car = $cars->record(3);
	 * 		print $my_car->year;
	 * 
	 * @return MySQL_Record Specified record
	 * @param Integer $id Record ID
	 */
	function record($id){
		$this->database->select();
		$query = sprintf("SELECT * FROM `%s` WHERE `id` = '%s'", $this->name, $this->mysql->escape($id));
		$records = $this->mysql->query($query);
		if ( isset($records[0]) ) return new MySQL_Record($this, $records[0]);
		return false;
		
	}
	
	/**
	 * Delete a specific record
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$cars->deleteRecord(3);
	 * 
	 * @return Bool True on success, false on failure
	 * @param Integer $id Record ID
	 */
	function deleteRecord($id){
		$this->database->select();
		return $this->mysql->query("DELETE FROM `$this->name` WHERE `id` = '$id'");
		
	}
	
	/**
	 * Get the number of records in the table
	 * eg
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		print $cars->recordCount();
	 * 
	 * @return Integer Record count
	 */
	function recordCount(){
		$this->database->select();
		$records = $this->mysql->query("SELECT COUNT(*) FROM `$this->name`");
		return $records[0][0];
	}
}

/**
 * @package MySQL_Lite
 */
class MySQL_Field {
	private $name = '';
	private $type = '';
	private $table = null;
	private $mysql = null;
	
	/**
	 * @ignore
	 */
	function __construct($table, $name, $type){
		$this->table = $table;
		$this->name = $name;
		$this->type = $type;
		$this->mysql = $table->mysql();
	}
	
	/**
	 * Set the field name and SQL type
	 * 
	 * @return Bool True on success, false on failure
	 * @param String $newName Field name
	 * @param Object $newType Field SQL type
	 */
	function alter($newName, $newType){
		$this->table->database()->select();
		if ( $this->mysql->query("ALTER TABLE `{$this->table->name()}` CHANGE `$this->name` `$newName` $newType") === false ) return false;
		$this->name = $newName;
		$this->type = $newType;
		return true;
	}
	
	/**
	 * Get/set the field name
	 * 
	 * @return String Field name
	 * @param String $newName[optional] New field name
	 */
	function name($newName=''){
		if ( $newName ){
			if ( $this->alter($newName, $this->type) === false ) return false;
		}
		return $this->name;
	}
	
	/**
	 * Get/set the field SQL type
	 * 
	 * @return String Field SQL type
	 * @param String $newType[optional] New field SQL type
	 */
	function type($newType=''){
		if ( $newType ){
			if ( $this->alter($this->name, $newType) === false ) return false;
		}
		return $this->type;
	}
}

/**
 * @package MySQL_Lite
 */
class MySQL_Record {
	private $data = array();
	private $table = null;
	private $mysql = null;
	
	/**
	 * @ignore
	 */
	function __construct($table, $sqlRecord){
		$this->table = $table;
		$this->data = $sqlRecord;
		$this->mysql = $table->mysql();
	}
	
	/**
	 * @ignore
	 */	
	function __get($var){
		return $this->data[$var];
	}
	
	/**
	 * @ignore
	 */
	function __set($var, $val){
		$this->table->database()->select();
		if ( ! $this->mysql->query("UPDATE `$this->table->name()` SET `$var` = '$val' WHERE `id`='$this->id'") ) return false;
		$this->data[$var] = $val;
	}
	
	/**
	 * Update a record
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$car = $cars->get(3);
	 * 		$data = array(
	 * 			'year'		=> '2007',
	 * 			'colour'	=> 'Blue'
	 *		);
	 * 		$car->update($data);
	 * Equivalent to
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$car = $cars->get(3);
	 * 		$car->year		= 2007;
	 * 		$car->colour	= 'blue';
	 * 
	 * @return Bool True on success, false on failure
	 * @param Object $sqlRecord
	 */
	function update($sqlRecord){
		$mySets = array();
		foreach ( $sqlRecord as $field => $val ){			
			$mySets[] = "`$field` = '$val'";
		}
		$setList = join(', ', $mySets);
		$this->table->database()->select();
		if ( ! $this->mysql->query("UPDATE `$this->table->name()` SET $setList WHERE `id`='$this->id'") ) return false;
		$this->data = $sqlRecord;
		return true;
	}
	
	/**
	 * Delete this record.
	 * eg.
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$cars->record(3)->delete();
	 * Equivalent to
	 * 		$cars = $mysql->database('automotive')->table('cars');
	 * 		$cars->deleteRecord(3);
	 * 
	 * @return Bool True on success, false on failure
	 */
	function delete(){
		return $this->table->dropRecord($this->id);
	}
}
